package com.apes.fa.report;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

/**
 * @Title: FaAssetDepreciationDao
 * @Description:
 * @author: ZhengZeXian
 * @create: 2021/12/22 15:04
 */
@Component
public class FaAssetDepreciationDao extends ApesDao {

    /**
     * 根据年月 YYYYMM 和公司 查询数据
     * YMYZ		0		月末原值
     * YMZJ		0		月末折旧
     * BYZJ		0		本月增加
     * ZJZJ		0		本月折旧增加
     * BYJS		0		本月减少
     * JSZJ		0		本月折旧减少
     * YTZJ		0		本月已提折旧
     * @param param
     * @return
     */
    public JSONArray queryAsset(JSONObject param){

        String sSQL = "SELECT YEARMONTH, COMPANY_ID, COST_CENTER_ID, FA_CATEGORY_ID, SUM(YMYZ) YMYZ, SUM(YMZJ) YMZJ, SUM(BYZJ) BYZJ, SUM(ZJZJ) ZJZJ, SUM(BYJS) BYJS, SUM(JSZJ) JSZJ, SUM(YTZJ) YTZJ FROM (\n" +
                "      SELECT TO_CHAR(A.BOOKKEEPING_DATE, 'yyyymm') YEARMONTH, A.COMPANY_ID, A.COST_CENTER_ID, B.FA_CATEGORY_ID,\n" +
                "    0 YMYZ, 0 YMZJ,SUM(A.ORIGINAL_VALUE_INCREASED) BYZJ, 0 ZJZJ, 0 BYJS, 0 JSZJ, 0 YTZJ\n" +
                "    FROM FA_CARD_ACCOUNT A, FA_CARD B\n" +
                "       WHERE A.FA_CARD_ID = B.ID AND A.SUMMARY = '1' --添加卡片\n" +
                "       GROUP BY TO_CHAR(A.BOOKKEEPING_DATE, 'yyyymm'), A.COMPANY_ID, A.COST_CENTER_ID, B.FA_CATEGORY_ID\n" +
                "       UNION ALL\n" +
                "      SELECT TO_CHAR(A.BOOKKEEPING_DATE, 'yyyymm') YEARMONTH, A.COMPANY_ID, A.COST_CENTER_ID, B.FA_CATEGORY_ID,\n" +
                "    0 YMYZ, 0 YMZJ,0 BYZJ, SUM(A.INCREASE_DEPRECIATION) ZJZJ, 0 BYJS, 0 JSZJ,0 YTZJ\n" +
                "    FROM FA_CARD_ACCOUNT A, FA_CARD B\n" +
                "       WHERE A.FA_CARD_ID = B.ID AND A.SUMMARY = '2'  --提取折旧\n" +
                "       GROUP BY TO_CHAR(A.BOOKKEEPING_DATE, 'yyyymm'), A.COMPANY_ID, A.COST_CENTER_ID, B.FA_CATEGORY_ID\n" +
                "    UNION ALL\n" +
                "      SELECT TO_CHAR(A.BOOKKEEPING_DATE, 'yyyymm') YEARMONTH, A.COMPANY_ID, A.COST_CENTER_ID, B.FA_CATEGORY_ID,\n" +
                "    0 YMYZ, 0 YMZJ,0 BYZJ, 0 ZJZJ, SUM(A.ORIGINAL_VALUE_DECREASE) BYJS, SUM(A.REDUCE_DEPRECIATION) JSZJ,0 YTZJ\n" +
                "    FROM FA_CARD_ACCOUNT A, FA_CARD B\n" +
                "       WHERE A.FA_CARD_ID = B.ID AND A.SUMMARY = '3' --盘亏                      \n" +
                "       GROUP BY TO_CHAR(A.BOOKKEEPING_DATE, 'yyyymm'), A.COMPANY_ID, A.COST_CENTER_ID, B.FA_CATEGORY_ID\n" +
                "     UNION ALL\n" +
                "      SELECT TO_CHAR(A.BOOKKEEPING_DATE, 'yyyymm') YEARMONTH, A.COMPANY_ID, A.COST_CENTER_ID, B.FA_CATEGORY_ID,\n" +
                "    0 YMYZ, 0 YMZJ,0 BYZJ, SUM(A.INCREASE_DEPRECIATION) ZJZJ, SUM(A.ORIGINAL_VALUE_DECREASE) BYJS,0 JSZJ,0 YTZJ\n" +
                "    FROM FA_CARD_ACCOUNT A, FA_CARD B\n" +
                "       WHERE A.FA_CARD_ID = B.ID AND A.SUMMARY in ('4','5') --报废,投资转出\n" +
                "       GROUP BY TO_CHAR(A.BOOKKEEPING_DATE, 'yyyymm'), A.COMPANY_ID, A.COST_CENTER_ID, B.FA_CATEGORY_ID\n" +
                " ) A WHERE A.YEARMONTH = :YEARMONTH AND A.COMPANY_ID = :COMPANY_ID GROUP BY YEARMONTH,COMPANY_ID, COST_CENTER_ID, FA_CATEGORY_ID" ;

        return toJSONArray(find(sSQL, param));
    }

}
